package com.gomefinance.excelutils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;


public class ExcelUtils {
	
	public static final String TRUE = "true";
	public static final String FALSE = "false";
	public static final String ALL = "全部";

 	/**
	 * 更具传入的数据生成Excel数据文件，模板为2007模板
	 * @param lisObj 数据列
	 * @param columnTitle 列的名称    String[] columnTitle = {"商机编号","商机名称","开始日期","商机阶段","商机状态","商机池类型","商机来源","创建人姓名","创建时间"};
		@param columnName 列列对应的字段名   String[] columnName = {"busNo","busName","startDate","busStep","busStatus","portType","busSource","createrName","createDate"};
	 * @param tileRow 列名的所在的行，起始行为0
	 * @param modelFile 模板路径和名称
	 * @param createFile 生成文件的路径和名称
	 * @return
	 * @throws IOException 
	 */
	public static void expExcel(List<Map<String, Object>> lisObj, String[] columnTitle, String[] columnNames,int tileRow, String modelFile, String createFile) throws IOException{
		
		POIFSFileSystem fs  =new POIFSFileSystem(new FileInputStream(modelFile));   
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		
		//遍历行
		for(int i=0; i<lisObj.size(); i++){
			//讲Map转为List
			Map<String, Object> objMap = lisObj.get(i);
			Iterator iter = objMap.entrySet().iterator();
			
			//行序号为标题所在行的下一行+当前的行数
			int rowNo = tileRow + 1 + i;
			//获取行
			HSSFRow row = sheet.getRow(rowNo);   
			
			//遍历列
			while(iter.hasNext()) {
				Entry entry = (Entry)iter.next();
				//列
				HSSFCell cell = null;
				//根据列名获取当前值所在的列
				String columnKey = (String)entry.getKey();
				for(int t=0;t<columnNames.length;t++){
					String columnName = columnNames[t];
					if(columnName != null && columnKey.equalsIgnoreCase(columnName)){
						//获取列
						cell = row.getCell((short)t);
					}
				}
				
				if(cell == null){
					continue;
				}
				
				//获取当前单元格内容
				String str = cell.getStringCellValue();
				
				String data = (String) entry.getValue();
				if(data == null){
					data = "";
				}
				//替换单元格内容
				str = str.replace("",  data);
				
				//写入单元格内容
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(str);  
			}
		}
		
		//创建列标题
		for(int m=0; m<columnTitle.length; m++){
			//获取行
			HSSFRow row = sheet.getRow(tileRow);
			//获取列
			HSSFCell cell = row.getCell((short)m);
			
			//获取当前单元格内容
			String str = cell.getStringCellValue();
			
			String title = (String) columnTitle[m];
			if(title == null){
				title = "";
			}
			//替换单元格内容
			str = str.replace("",  title);
			
			//写入单元格内容
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(str);  
		}
		
		// 输出文件   
		FileOutputStream fileOut = new FileOutputStream(createFile);   
		wb.write(fileOut);   
		fileOut.close();   
	}
	
	/**
	 * 根据模板插入数据,模板中存在一行存放字段名
	 * @param lisObj
	 * @param tileRow 字段名所在的行
	 * @param modelFile
	 * @param createFile
	 * @throws IOException
	 */
	public static void expExcel(List<Map<String, Object>> lisObj, int tileRow, String modelFile, String createFile) throws IOException{
		
		POIFSFileSystem fs  =new POIFSFileSystem(new FileInputStream(modelFile));   
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		
		//创建列标题
		//获取行
		HSSFRow titleRow = sheet.getRow(tileRow);
		int tileCount = titleRow.getPhysicalNumberOfCells();
		String[] titelArr = new String[tileCount];;
		for(int u=0; u<tileCount; u++){
			HSSFCell cell = titleRow.getCell(u);
			titelArr[u] = cell.getStringCellValue();
		}
		
		//遍历行
		for(int i=0; i<lisObj.size(); i++){
			//讲Map转为List
			Map<String, Object> objMap = lisObj.get(i);
			Iterator iter = objMap.entrySet().iterator();
			
			//行序号为标题所在行的下一行+当前的行数
			int rowNo = tileRow + 1 + i;
			//获取行
			HSSFRow row = sheet.getRow(rowNo);   
			
			//遍历列
			while(iter.hasNext()) {
				Entry entry = (Entry)iter.next();
				//列
				HSSFCell cell = null;
				//根据列名获取当前值所在的列
				String columnKey = (String)entry.getKey();
				for(int t=0;t<titelArr.length;t++){
					String columnName = titelArr[t];
					if(columnName != null && columnKey.equalsIgnoreCase(columnName)){
						//获取列
						cell = row.getCell((short)t);
					}
				}
				
				if(cell == null){
					continue;
				}
				
				//获取当前单元格内容
				String str = cell.getStringCellValue();
				
				String data = (String) entry.getValue();
				if(data == null){
					data = "";
				}
				//替换单元格内容
				str = str.replace("",  data);
				
				//写入单元格内容
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(str);  
			}
		}
		
		// 输出文件   
		FileOutputStream fileOut = new FileOutputStream(createFile);   
		wb.write(fileOut);   
		fileOut.close();   
	}
 	
	/**
	 * 读取EXCEL某页、某行的某个单元的值
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @param sourceFilePath
	 * @return
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public static String getColumnValue(int sheetNum, int rowNum, int cellNum, String sourceFilePath) throws FileNotFoundException, IOException {
		String cellValue;
		
		POIFSFileSystem fs  =new POIFSFileSystem(new FileInputStream(sourceFilePath));   
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(sheetNum);
		//获取单元格内容
		HSSFRow row = sheet.getRow(rowNum);   
		HSSFCell cell = row.getCell(cellNum);
		cellValue = cell.getStringCellValue();
		
		return cellValue;
	}

	/**
	 * 合并单元格后给合并后的单元格加边框
	 * 
	 * @param region
	 * @param cs
	 */
	public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs, XSSFSheet sheet)
	{

		int toprowNum = region.getFirstRow();
		for (int i = toprowNum; i <= region.getLastRow(); i++)
		{
			XSSFRow row = sheet.getRow(i);
			for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)
			{
				XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
												// (short) j);
				cell.setCellStyle(cs);
			}
		}
	}

	/**
	 * 设置表头的单元格样式
	 * 
	 * @return
	 */
	public static HSSFCellStyle getHeadStyle(HSSFWorkbook workBook)
	{
		// 创建单元格样式
		HSSFCellStyle cellStyle = workBook.createCellStyle();
		// 设置单元格的背景颜色为淡蓝色
		cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		// 设置单元格居中对齐
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		HSSFFont font = workBook.createFont();
		// 设置字体加粗
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("黑体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}

	/**
	 * 设置表体的单元格样式
	 * 
	 * @return
	 */
	public static HSSFCellStyle getBodyStyle(HSSFWorkbook workBook)
	{
		// 创建单元格样式
		HSSFCellStyle cellStyle = workBook.createCellStyle();
		// 设置单元格居中对齐
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		HSSFFont font = workBook.createFont();
		// 设置字体加粗
//		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("黑体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}
	
	/**
	 * 设置表体的标题样式
	 * 
	 * @return
	 */
	public static HSSFCellStyle getTitleStyle(HSSFWorkbook workBook)
	{
		// 创建单元格样式
		HSSFCellStyle cellStyle = workBook.createCellStyle();
		// 设置单元格居中对齐
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		HSSFFont font = workBook.createFont();
		// 设置字体加粗
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("黑体");
		font.setFontHeight((short) 300);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}
	
	public static CellStyle getHeadStyle(SXSSFWorkbook workBook)
	{
		// 鍒涘缓鍗曞厓鏍兼牱寮�
		CellStyle cellStyle = workBook.createCellStyle();
		// 璁剧疆鍗曞厓鏍肩殑鑳屾櫙棰滆壊涓烘贰钃濊壊
		cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
		cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		// 璁剧疆鍗曞厓鏍煎眳涓榻�
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 璁剧疆鍗曞厓鏍煎瀭鐩村眳涓榻�
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 鍒涘缓鍗曞厓鏍煎唴瀹规樉绀轰笉涓嬫椂鑷姩鎹㈣
		cellStyle.setWrapText(false);
		// 璁剧疆鍗曞厓鏍煎瓧浣撴牱寮�
		Font font = workBook.createFont();
		// 璁剧疆瀛椾綋鍔犵矖
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("榛戜綋");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 璁剧疆鍗曞厓鏍艰竟妗嗕负缁嗙嚎鏉�
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}
	
	public static CellStyle getBodyStyle(SXSSFWorkbook workBook)
	{
		// 鍒涘缓鍗曞厓鏍兼牱寮�
		CellStyle cellStyle = workBook.createCellStyle();
		// 璁剧疆鍗曞厓鏍煎眳涓榻�
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 璁剧疆鍗曞厓鏍煎瀭鐩村眳涓榻�
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 鍒涘缓鍗曞厓鏍煎唴瀹规樉绀轰笉涓嬫椂鑷姩鎹㈣
		cellStyle.setWrapText(false);
		// 璁剧疆鍗曞厓鏍煎瓧浣撴牱寮�
		Font font = workBook.createFont();
		// 璁剧疆瀛椾綋鍔犵矖
//		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("榛戜綋");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 璁剧疆鍗曞厓鏍艰竟妗嗕负缁嗙嚎鏉�
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}
	
	public static CellStyle getTitleStyle(SXSSFWorkbook workBook)
	{
		// 鍒涘缓鍗曞厓鏍兼牱寮�
		CellStyle cellStyle = workBook.createCellStyle();
		// 璁剧疆鍗曞厓鏍煎眳涓榻�
		cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		// 璁剧疆鍗曞厓鏍煎瀭鐩村眳涓榻�
		cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		// 鍒涘缓鍗曞厓鏍煎唴瀹规樉绀轰笉涓嬫椂鑷姩鎹㈣
		cellStyle.setWrapText(false);
		// 璁剧疆鍗曞厓鏍煎瓧浣撴牱寮�
		Font font = workBook.createFont();
		// 璁剧疆瀛椾綋鍔犵矖
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("榛戜綋");
		font.setFontHeight((short) 300);
		cellStyle.setFont(font);
		// 璁剧疆鍗曞厓鏍艰竟妗嗕负缁嗙嚎鏉�
		cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		return cellStyle;
	}
	
	
}
